package cn.iocoder.yudao.module.system.dal.mysql.useractivity;

import cn.iocoder.yudao.framework.common.pojo.PageParam;
import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.framework.mybatis.core.mapper.BaseMapperX;
import cn.iocoder.yudao.framework.mybatis.core.query.LambdaQueryWrapperX;
import cn.iocoder.yudao.module.system.dal.dataobject.useractivity.UserDailyActivityDO;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

/**
 * 用户每日活跃明细 Mapper
 *
 * @author 系统开发团队
 */
@Mapper
public interface UserDailyActivityMapper extends BaseMapperX<UserDailyActivityDO> {

    /**
     * 根据用户ID和日期查询
     * 注意：由于可能存在重复数据，使用 selectList 并返回第一条记录
     */
    default UserDailyActivityDO selectByUserIdAndDate(Long userId, LocalDate activityDate) {
        List<UserDailyActivityDO> list = selectList(new LambdaQueryWrapperX<UserDailyActivityDO>()
                .eq(UserDailyActivityDO::getUserId, userId)
                .eq(UserDailyActivityDO::getActivityDate, activityDate)
                .orderByAsc(UserDailyActivityDO::getId) // 按ID升序，取最早创建的记录
                .last("LIMIT 1")); // 只取第一条
        
        if (list == null || list.isEmpty()) {
            return null;
        }
        
        // 如果查询到多条记录，记录警告日志（这不应该发生）
        List<UserDailyActivityDO> allList = selectList(new LambdaQueryWrapperX<UserDailyActivityDO>()
                .eq(UserDailyActivityDO::getUserId, userId)
                .eq(UserDailyActivityDO::getActivityDate, activityDate));
        if (allList.size() > 1) {
            org.slf4j.LoggerFactory.getLogger(UserDailyActivityMapper.class)
                .warn("[selectByUserIdAndDate] 检测到重复数据: userId={}, activityDate={}, 数量={}, 请尽快清理重复数据！", 
                      userId, activityDate, allList.size());
        }
        
        return list.get(0);
    }

    /**
     * 查询指定日期的活跃用户列表
     */
    default List<UserDailyActivityDO> selectActiveUsersByDate(LocalDate activityDate) {
        return selectList(new LambdaQueryWrapperX<UserDailyActivityDO>()
                .eq(UserDailyActivityDO::getActivityDate, activityDate)
                .eq(UserDailyActivityDO::getIsActive, true)
                .orderByDesc(UserDailyActivityDO::getLastLoginTime));
    }

    /**
     * 查询活跃用户列表（支持多条件筛选）
     */
    default List<UserDailyActivityDO> selectActiveUsers(LocalDate activityDate, LocalDate startDate, LocalDate endDate, Integer userType, Integer limit) {
        LambdaQueryWrapperX<UserDailyActivityDO> wrapper = new LambdaQueryWrapperX<UserDailyActivityDO>()
                .eqIfPresent(UserDailyActivityDO::getActivityDate, activityDate)
                .betweenIfPresent(UserDailyActivityDO::getActivityDate, startDate, endDate)
                .eqIfPresent(UserDailyActivityDO::getUserType, userType)
                .eq(UserDailyActivityDO::getIsActive, true)
                .orderByDesc(UserDailyActivityDO::getLastLoginTime);
        
        if (limit != null && limit > 0) {
            wrapper.last("LIMIT " + limit);
        }
        
        return selectList(wrapper);
    }

    /**
     * 查询指定日期范围内的用户活跃记录
     */
    default List<UserDailyActivityDO> selectByDateRange(LocalDate startDate, LocalDate endDate) {
        return selectList(new LambdaQueryWrapperX<UserDailyActivityDO>()
                .betweenIfPresent(UserDailyActivityDO::getActivityDate, startDate, endDate)
                .orderByDesc(UserDailyActivityDO::getActivityDate));
    }

    /**
     * 查询指定用户在日期范围内的活跃记录
     */
    default List<UserDailyActivityDO> selectByUserIdAndDateRange(Long userId, LocalDate startDate, LocalDate endDate) {
        return selectList(new LambdaQueryWrapperX<UserDailyActivityDO>()
                .eq(UserDailyActivityDO::getUserId, userId)
                .betweenIfPresent(UserDailyActivityDO::getActivityDate, startDate, endDate)
                .orderByDesc(UserDailyActivityDO::getActivityDate));
    }

    /**
     * 统计指定日期的活跃用户数
     */
    @Select("SELECT COUNT(DISTINCT user_id) FROM ai_user_daily_activity WHERE activity_date = #{activityDate} AND is_active = 1")
    Long countActiveUsersByDate(@Param("activityDate") LocalDate activityDate);

    /**
     * 统计指定日期的新用户数
     */
    @Select("SELECT COUNT(DISTINCT user_id) FROM ai_user_daily_activity WHERE activity_date = #{activityDate} AND is_new_user = 1")
    Long countNewUsersByDate(@Param("activityDate") LocalDate activityDate);

    /**
     * 统计指定日期范围内的DAU
     */
    @Select("SELECT activity_date, COUNT(DISTINCT user_id) as dau FROM ai_user_daily_activity " +
            "WHERE activity_date BETWEEN #{startDate} AND #{endDate} AND is_active = 1 " +
            "GROUP BY activity_date ORDER BY activity_date")
    List<Map<String, Object>> selectDauByDateRange(@Param("startDate") LocalDate startDate, 
                                                   @Param("endDate") LocalDate endDate);

    /**
     * 统计指定日期范围内的WAU
     */
    @Select("SELECT YEARWEEK(activity_date) as week, COUNT(DISTINCT user_id) as wau FROM ai_user_daily_activity " +
            "WHERE activity_date BETWEEN #{startDate} AND #{endDate} AND is_active = 1 " +
            "GROUP BY YEARWEEK(activity_date) ORDER BY week")
    List<Map<String, Object>> selectWauByDateRange(@Param("startDate") LocalDate startDate, 
                                                   @Param("endDate") LocalDate endDate);

    /**
     * 统计指定日期范围内的MAU
     */
    @Select("SELECT DATE_FORMAT(activity_date, '%Y-%m') as month, COUNT(DISTINCT user_id) as mau FROM ai_user_daily_activity " +
            "WHERE activity_date BETWEEN #{startDate} AND #{endDate} AND is_active = 1 " +
            "GROUP BY DATE_FORMAT(activity_date, '%Y-%m') ORDER BY month")
    List<Map<String, Object>> selectMauByDateRange(@Param("startDate") LocalDate startDate, 
                                                   @Param("endDate") LocalDate endDate);

    /**
     * 统计用户类型分布
     */
    @Select("SELECT user_type, COUNT(DISTINCT user_id) as count FROM ai_user_daily_activity " +
            "WHERE activity_date = #{activityDate} AND is_active = 1 " +
            "GROUP BY user_type")
    List<Map<String, Object>> selectUserTypeDistribution(@Param("activityDate") LocalDate activityDate);

    /**
     * 统计设备类型分布
     */
    @Select("SELECT device_type, COUNT(DISTINCT user_id) as count FROM ai_user_daily_activity " +
            "WHERE activity_date = #{activityDate} AND is_active = 1 " +
            "GROUP BY device_type")
    List<Map<String, Object>> selectDeviceTypeDistribution(@Param("activityDate") LocalDate activityDate);

    /**
     * 统计地域分布（Top N）
     */
    @Select("SELECT region, COUNT(DISTINCT user_id) as count FROM ai_user_daily_activity " +
            "WHERE activity_date = #{activityDate} AND is_active = 1 " +
            "GROUP BY region ORDER BY count DESC LIMIT #{topN}")
    List<Map<String, Object>> selectTopRegions(@Param("activityDate") LocalDate activityDate, 
                                               @Param("topN") Integer topN);

    /**
     * 统计连续活跃用户数
     */
    @Select("SELECT consecutive_days, COUNT(DISTINCT user_id) as count FROM ai_user_daily_activity " +
            "WHERE activity_date = #{activityDate} AND is_active = 1 AND consecutive_days >= #{minDays} " +
            "GROUP BY consecutive_days ORDER BY consecutive_days")
    List<Map<String, Object>> selectConsecutiveActiveUsers(@Param("activityDate") LocalDate activityDate, 
                                                           @Param("minDays") Integer minDays);

    /**
     * 统计平均在线时长
     */
    @Select("SELECT AVG(online_duration) FROM ai_user_daily_activity " +
            "WHERE activity_date = #{activityDate} AND is_active = 1 AND online_duration > 0")
    Double selectAvgOnlineDuration(@Param("activityDate") LocalDate activityDate);

    /**
     * 统计平均页面浏览数
     */
    @Select("SELECT AVG(page_views) FROM ai_user_daily_activity " +
            "WHERE activity_date = #{activityDate} AND is_active = 1 AND page_views > 0")
    Double selectAvgPageViews(@Param("activityDate") LocalDate activityDate);

    /**
     * 统计平均功能使用次数
     */
    @Select("SELECT AVG(feature_usage_count) FROM ai_user_daily_activity " +
            "WHERE activity_date = #{activityDate} AND is_active = 1 AND feature_usage_count > 0")
    Double selectAvgFeatureUsage(@Param("activityDate") LocalDate activityDate);

    /**
     * 查询高活跃用户（根据功能使用次数）
     */
    default List<UserDailyActivityDO> selectHighActiveUsers(LocalDate activityDate, Integer minFeatureUsage) {
        return selectList(new LambdaQueryWrapperX<UserDailyActivityDO>()
                .eq(UserDailyActivityDO::getActivityDate, activityDate)
                .eq(UserDailyActivityDO::getIsActive, true)
                .ge(UserDailyActivityDO::getFeatureUsageCount, minFeatureUsage)
                .orderByDesc(UserDailyActivityDO::getFeatureUsageCount));
    }

    /**
     * 查询回流用户（一段时间未活跃后重新活跃）
     */
    @Select("SELECT * FROM ai_user_daily_activity a " +
            "WHERE a.activity_date = #{activityDate} AND a.is_active = 1 " +
            "AND NOT EXISTS (SELECT 1 FROM ai_user_daily_activity b " +
            "WHERE b.user_id = a.user_id AND b.activity_date BETWEEN #{startDate} AND #{endDate} AND b.is_active = 1)")
    List<UserDailyActivityDO> selectReturnUsers(@Param("activityDate") LocalDate activityDate,
                                                @Param("startDate") LocalDate startDate,
                                                @Param("endDate") LocalDate endDate);

    /**
     * 删除过期数据
     */
    default int deleteExpiredData(LocalDate beforeDate) {
        return delete(new LambdaQueryWrapperX<UserDailyActivityDO>()
                .lt(UserDailyActivityDO::getActivityDate, beforeDate));
    }

    /**
     * 分页查询用户活跃明细
     */
    default PageResult<UserDailyActivityDO> selectPage(LocalDate startDate, LocalDate endDate, 
                                                       Long userId, Boolean isActive, Integer userType,
                                                       int pageNo, int pageSize) {
        PageParam pageParam = new PageParam();
        pageParam.setPageNo(pageNo);
        pageParam.setPageSize(pageSize);
        return selectPage(pageParam, new LambdaQueryWrapperX<UserDailyActivityDO>()
                .betweenIfPresent(UserDailyActivityDO::getActivityDate, startDate, endDate)
                .eqIfPresent(UserDailyActivityDO::getUserId, userId)
                .eqIfPresent(UserDailyActivityDO::getIsActive, isActive)
                .eqIfPresent(UserDailyActivityDO::getUserType, userType)
                .orderByDesc(UserDailyActivityDO::getActivityDate)
                .orderByDesc(UserDailyActivityDO::getLastLoginTime));
    }
}